package bdddaosimpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import bdd.beans.*;
import bdddaos.*;
import java.util.Date;

public class DAOCreneauImpl implements DAOCreneau{

	private DBLink dblink;
	
	public DAOCreneauImpl() {
		dblink = new DBLink();
	}

	public Creneau save(Creneau o) {
		try {
			this.dblink.connexion();
			DateFormat format = new SimpleDateFormat("dd-MM-yy");
			String r = "INSERT INTO CRENEAU (NUMSALLE, NUMPROMOTION, NUMENSEIGNANT, NUMTYPE, NUMEC, HORAIREDEBUT, HORAIREFIN, IDDATE)" +
			" VALUES ("+o.getSalle().getNumsalle()+ ", "+ o.getPromotion().getNumpromotion() +", "+o.getEnseignant().getNumenseignant()+", "+o.getType().getNumtype()+", "+o.getEc().getNumec()+"" +
					", "+o.getHorairedebut()+", "+o.getHorairefin()+", "+format.format(o.getJours().getIddate())+")";
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return o;
	}

	public Creneau find(Integer NumSalle,Integer NumEnseignant,Integer NumType,Integer NumEC,Jours idDate) {
		try {
			this.dblink.connexion();
			DateFormat format = new SimpleDateFormat("dd-MM-yy");
			String r = "SELECT * FROM CRENEAU WHERE NUMSALLE = " + NumSalle + " AND NUMENSEIGNANT = " + NumEnseignant + " AND NUMTYPE = " + NumType + " AND NUMEC = " + NumEC + " AND IDDATE='"+format.format(idDate.getIddate())+"'";
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			ResultSet rs = this.dblink.getPst().executeQuery();
			if(rs.next()) {
				Creneau retour = new Creneau();
				retour.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
				retour.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
				retour.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
				retour.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
				retour.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
				retour.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
				retour.setHorairedebut(rs.getInt("HORAIREDEBUT"));
				retour.setHorairefin(rs.getInt("HORAIREFIN"));
				
				this.dblink.getPst().close();
				this.dblink.deconnexion();
				
				return retour;
			}		
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te" + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return null;
	}
	
	public List<Creneau> findByDate(Jours idDate) {

		List<Creneau> Lstretour = new ArrayList<Creneau>();
		try {
			this.dblink.connexion();
			DateFormat format = new SimpleDateFormat("dd-MM-yy");
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM CRENEAU WHERE IDDATE = '" + format.format(idDate.getIddate())+"'"));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				Creneau retour = new Creneau();
				retour.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
				retour.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
				retour.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
				retour.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
				retour.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
				retour.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
				retour.setHorairedebut(rs.getInt("HORAIREDEBUT"));
				retour.setHorairefin(rs.getInt("HORAIREFIN"));
				Lstretour.add(retour);
			}		
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te" + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return Lstretour;
	}
	
	public List<Creneau> findAll() {

		List<Creneau> Lstretour = new ArrayList<Creneau>();
		try {
			this.dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM CRENEAU"));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				Creneau retour = new Creneau();
				retour.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
				retour.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
				retour.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
				retour.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
				retour.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
				retour.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
				retour.setHorairedebut(rs.getInt("HORAIREDEBUT"));
				retour.setHorairefin(rs.getInt("HORAIREFIN"));
				Lstretour.add(retour);
			}		
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te" + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return Lstretour;
	}
    
	public void update(Creneau o) {
		try {
			this.dblink.connexion();
			//String r = "UPDATE CRENEAU SET NUMSALLE='"+o.getSalle().setNumsalle()+ "', NUMPROMOTION='"+o.getPromotion().getNumpromotion() + "' WHERE NUMCONTRAT="+o.getNumcontrat();
			String r = "TA MERE";
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
	}

	public void delete(Creneau cr) {
		try {
			this.dblink.connexion();
			DateFormat format = new SimpleDateFormat("dd-MM-yy");
			String r = "DELETE FROM CRENEAU WHERE NUMSALLE = " + cr.getSalle().getNumsalle() + " AND HORAIREDEBUT ="+cr.getHorairedebut()+" AND HORAIREFIN="+cr.getHorairefin()+" IDDATE="+format.format(cr.getJours().getIddate());
			//String r = "TA MERE";
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requête : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		
	}

	public List<Creneau> findByEnseignant(Integer numEnseignant) {
		List<Creneau> liste = new ArrayList<Creneau>();
		dblink.connexion();
		
		String query = "select distinct NUMEC from creneau where numenseignant = "+numEnseignant;
		
		try {
			PreparedStatement pst = dblink.getConnection().prepareStatement(query); 
			ResultSet rs = pst.executeQuery();
			
			/*while(rs.next()) {
				Creneau c = new Creneau();
				
				DAOSalle daoSalle = new DAOSalleImpl();
				c.setSalle(daoSalle.find(rs.getInt("NUMSALLE")));
				DAOPromotion daoPromotion = new DAOPromotionImpl();
				c.setPromotion(daoPromotion.find(rs.getInt("NUMPROMOTION")));
				DAOEnseignant daoEnseignant = new DAOEnseignantImpl();
				c.setEnseignant(daoEnseignant.find(rs.getInt("NUMENSEIGNANT")));
				DAOType daoType = new DAOTypeImpl();
				c.setType(daoType.find(rs.getInt("NUMTYPE")));
				DAOEc daoEc = new DAOEcImpl();
				c.setEc(daoEc.find(rs.getInt("NUMEC")));
				c.setHorairedebut(rs.getInt("HORAIREDEBUT"));
				c.setHorairefin(rs.getInt("HORAIREFIN"));
				liste.add(c);
			}*/
                        while(rs.next()) {
				Creneau c = new Creneau();

				DAOEc daoEc = new DAOEcImpl();
				c.setEc(daoEc.find(rs.getInt("NUMEC")));
				liste.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			dblink.deconnexion();
		}
		
		return liste;
	}

    public List<Creneau> findByEc(Integer numEc) {
        List<Creneau> liste = new ArrayList<Creneau>();
        dblink.connexion();

        String query = "select * from Creneau where numec = "+numEc;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Creneau c = new Creneau();

                        c.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
                        c.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
                        c.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
                        c.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
                        c.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
                        c.setHorairedebut(rs.getInt("HORAIREDEBUT"));
                        c.setHorairefin(rs.getInt("HORAIREFIN"));
                        c.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }

    public Creneau find(Integer id) {
        throw new UnsupportedOperationException("Not supported.");
    }

    public List<Creneau> findByJour(Date jour) {
        List<Creneau> liste = new ArrayList<Creneau>();
        dblink.connexion();

        String query = "select * from Creneau where IDDATE = "+jour;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Creneau c = new Creneau();

                        c.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
                        c.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
                        c.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
                        c.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
                        c.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
                        c.setHorairedebut(rs.getInt("HORAIREDEBUT"));
                        c.setHorairefin(rs.getInt("HORAIREFIN"));
                        c.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }

    public List<Creneau> findByPromotion(Integer numPromotion) {
        List<Creneau> liste = new ArrayList<Creneau>();
        dblink.connexion();

        String query = "select * from Creneau where numpromotion = "+numPromotion;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Creneau c = new Creneau();

                        c.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
                        c.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
                        c.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
                        c.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
                        c.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
                        c.setHorairedebut(rs.getInt("HORAIREDEBUT"));
                        c.setHorairefin(rs.getInt("HORAIREFIN"));
                        c.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }

    public List<Creneau> findBySalle(Integer numSalle) {
        List<Creneau> liste = new ArrayList<Creneau>();
        dblink.connexion();

        String query = "select * from Creneau where numsalle = "+numSalle;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Creneau c = new Creneau();

                        c.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
                        c.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
                        c.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
                        c.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
                        c.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
                        c.setHorairedebut(rs.getInt("HORAIREDEBUT"));
                        c.setHorairefin(rs.getInt("HORAIREFIN"));
                        c.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }

    public List<Creneau> findByType(Integer numType) {
        List<Creneau> liste = new ArrayList<Creneau>();
        dblink.connexion();

        String query = "select * from Creneau where numtype = "+numType;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Creneau c = new Creneau();

                        c.setSalle(new DAOSalleImpl().find(rs.getInt("NUMSALLE")));
                        c.setPromotion(new DAOPromotionImpl().find(rs.getInt("NUMPROMOTION")));
                        c.setEnseignant(new DAOEnseignantImpl().find(rs.getInt("NUMENSEIGNANT")));
                        c.setType(new DAOTypeImpl().find(rs.getInt("NUMTYPE")));
                        c.setEc(new DAOEcImpl().find(rs.getInt("NUMEC")));
                        c.setHorairedebut(rs.getInt("HORAIREDEBUT"));
                        c.setHorairefin(rs.getInt("HORAIREFIN"));
                        c.setJours(new DAOJoursImpl().find(rs.getDate("IDDATE")));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }


}
